package com.easyapi.server.database.mysql;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidPooledConnection;
import com.easyapi.common.contants.MySQLConstants;
import com.easyapi.common.enums.StatusCode;
import com.easyapi.common.exception.BizException;
import com.easyapi.common.vo.metadata.TableStruct;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

/**
 * @author javadadi
 */
public class MySQLMetadata {
    private static final Logger log = LoggerFactory.getLogger(MySQLMetadata.class);

    /**
     * 获取所有的数据库
     *
     * @param dataSource
     * @return
     */
    public static List<String> listAllDatabases(DruidDataSource dataSource) {
        List<String> list = new ArrayList<>();
        DruidPooledConnection connection = null;
        PreparedStatement statement = null;
        try {
            connection = dataSource.getConnection();
            statement = connection.prepareStatement(MySQLConstants.SHOW_ALL_DATABASES);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                list.add(resultSet.getString(1));
            }
        } catch (SQLException e) {
            throw new BizException(StatusCode.OPERATION_ERROR);
        } finally {
            try {
                if (Objects.nonNull(connection)) {
                    connection.close();
                }
                if (Objects.nonNull(statement)) {
                    statement.close();
                }
            } catch (SQLException e) {
                log.error("close datasource connection error :{}", e.getMessage());
            }
        }
        return list;
    }

    /**
     * 获取数据库下所有的表
     *
     * @param dataSource
     * @param databasesName
     * @return
     */
    public static List<String> listAllTables(DruidDataSource dataSource, String databasesName) {
        List<String> list = new ArrayList<>();
        DruidPooledConnection connection = null;
        PreparedStatement statement = null;
        try {
            connection = dataSource.getConnection();
            String sql = String.format(MySQLConstants.SHOW_ALL_TABLES, databasesName);
            statement = connection.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                list.add(resultSet.getString(1));
            }
        } catch (SQLException e) {
            throw new BizException(StatusCode.OPERATION_ERROR);
        } finally {
            try {
                if (Objects.nonNull(connection)) {
                    connection.close();
                }
                if (Objects.nonNull(statement)) {
                    statement.close();
                }
            } catch (SQLException e) {
                log.error("close datasource connection error :{}", e.getMessage());
            }
        }
        return list;
    }

    /**
     * 获取表结构
     * @param dataSource
     * @param databasesName
     * @param tableName
     * @return
     */
    public static List<TableStruct> getTableStruct(DruidDataSource dataSource, String databasesName, String tableName) {
        List<TableStruct> list = new ArrayList<>();
        DruidPooledConnection connection = null;
        PreparedStatement statement = null;
        try {
            connection = dataSource.getConnection();
            String sql = String.format(MySQLConstants.SHOW_TABLE_STRUCTURE, databasesName, tableName);
            statement = connection.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                String columnName = resultSet.getString("COLUMN_NAME");
                String dataType = resultSet.getString("DATA_TYPE");
                String isNullable = resultSet.getString("IS_NULLABLE");
                String aDefault = resultSet.getString("COLUMN_DEFAULT");
                String columnDefault = Objects.isNull(aDefault) ? "" : aDefault;
                String key = resultSet.getString("COLUMN_KEY");
                Integer isPK = Objects.equals(key, "PRI") ? 1 : 0;
                String ex = resultSet.getString("EXTRA");
                String extra = Objects.isNull(ex) ? "" : ex;
                TableStruct build = TableStruct.builder()
                        .columnName(columnName)
                        .dataType(dataType)
                        .isNullable(isNullable)
                        .columnDefault(columnDefault)
                        .isPrimaryKey(isPK)
                        .extra(extra)
                        .build();
                list.add(build);
            }
        } catch (SQLException e) {
            throw new BizException(StatusCode.OPERATION_ERROR);
        } finally {
            try {
                if (Objects.nonNull(connection)) {
                    connection.close();
                }
                if (Objects.nonNull(statement)) {
                    statement.close();
                }
            } catch (SQLException e) {
                log.error("close datasource connection error :{}", e.getMessage());
            }
        }
        return list;
    }

    /**
     * 获取建表语句
     * @param dataSource
     * @param databasesName
     * @param tableName
     * @return
     */
    public static String getCreateTableSql(DruidDataSource dataSource, String databasesName, String tableName) {
        String createSQL = "";
        DruidPooledConnection connection = null;
        PreparedStatement statement = null;
        try {
            connection = dataSource.getConnection();
            String sql = String.format(MySQLConstants.SHOW_CREATE_TABLE, databasesName, tableName);
            statement = connection.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()){
                createSQL = resultSet.getString(2);
            }
        } catch (SQLException e) {
            log.error("get create sql error message:{}",e.getMessage());
            throw new BizException(StatusCode.OPERATION_ERROR);
        } finally {
            try {
                if (Objects.nonNull(connection)) {
                    connection.close();
                }
                if (Objects.nonNull(statement)) {
                    statement.close();
                }
            } catch (SQLException e) {
                log.error("close datasource connection error :{}", e.getMessage());
            }
        }
        return createSQL;
    }

    /**
     * 获取表数据
     * @param dataSource
     * @param databasesName
     * @param tableName
     * @return
     */
    public static List<List<String>> getTableData(DruidDataSource dataSource, String databasesName, String tableName) {
        List<List<String>> list = new ArrayList<>();
        DruidPooledConnection connection = null;
        PreparedStatement statement = null;
        try {
            connection = dataSource.getConnection();
            String sql = String.format(MySQLConstants.SHOW_TABLE_DATA, databasesName, tableName);
            statement = connection.prepareStatement(sql);
            ResultSet resultSet = statement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            List<String> columnNames = new ArrayList<>();
            for (int i = 1; i <= columnCount; i++) {
                columnNames.add(metaData.getColumnName(i));
            }
            list.add(columnNames);
            while (resultSet.next()) {
                ArrayList<String> arrayList = new ArrayList<>();
                for (int i = 1; i <= columnCount; i++) {
                    String setString = resultSet.getString(i);
                    String item = Objects.isNull(setString) ? "null" : setString;
                    arrayList.add(item);
                }
                list.add(arrayList);
            }
        } catch (SQLException e) {
            throw new BizException(StatusCode.OPERATION_ERROR);
        } finally {
            try {
                if (Objects.nonNull(connection)) {
                    connection.close();
                }
                if (Objects.nonNull(statement)) {
                    statement.close();
                }
            } catch (SQLException e) {
                log.error("close datasource connection error :{}", e.getMessage());
            }
        }
        return list;
    }

}
